"""
sqllite3与mysql之间的一些数据转换工具
"""
import threading
from queue import Queue

from facade.mysqlfacade import MysqlUtiles
from facade.sqlite3facade import Sqlite3Utiles
from xjlibrary.our_file_dir import BaseDir
from abc import ABC, abstractmethod


class DB3MysqlConvertion(ABC):
    def __init__(self):
        self.queueinput = Queue(300)

    @abstractmethod
    def input_sql_hook(self):
        pass

    @abstractmethod
    def input_row_hook(self, row):
        pass

    def conn_mysql(self, configfile, sesc):
        # 配置文件与默认配置文件key的转换
        keytransformdicts = {"port": "db_port", "host": "db_host", "passwd": "db_pw", "user": "db_user",
                             "db": "db_name",
                             "chartset": "db_charset"}
        # mysql 工具类
        self.myutil = MysqlUtiles(configfile, sesc, keytransformdicts=keytransformdicts)

    def process(self, DirPath):
        # args是关键字参数，需要加上名字，写成args=(self,)
        th1 = threading.Thread(target=DB3MysqlConvertion.db3_input, args=(self, DirPath))
        th1.daemon = True
        th1.start()

    def db3_input(self, DirPath):
        """
        db3读取
        :param DirPath:
        :return:
        """
        for filePath in BaseDir.get_dir_all_files(DirPath):
            # 获取sqlite处理对象，通过传入的db3文件
            sqliteutiles = Sqlite3Utiles().Sqlite3DBConnectFromFilePath(filePath, encoding="utf-8")
            # 通过hook钩子获取sql语句 且是一个select语句
            sql = self.input_sql_hook()
            # 按条返回数据
            for row in sqliteutiles.SelectFromSqlliteFetchOne(sql):
                # 将数据经过hook钩子处理后加入队列
                self.queueinput.put(self.input_row_hook(row))
            sqliteutiles.close()
        # 数据完成的结束标志
        print("完成标志EOF已写入")
        self.queueinput.put("EOF")

    def mysql_process(self):
        # args是关键字参数，需要加上名字，写成args=(self,)
        th1 = threading.Thread(target=DB3MysqlConvertion.mysql_input, args=(self,))
        th1.daemon = True
        th1.start()

    def mysql_input(self):
        sql = self.input_sql_hook()
        # 按条返回数据
        for row in self.myutil.SelectFromDBFetchOne(sql):
            # 将数据经过hook钩子处理后加入队列
            self.queueinput.put(self.input_row_hook(row))
        # 数据完成的结束标志
        print("完成标志EOF已写入")
        self.queueinput.put("EOF")

    def get_id(self, num=10000):
        """
        通过队列获取一批处理过的数据
        :param num:
        :return:
        """
        dateList = []
        for i in range(0, num):
            # 程序不会结束 会在队列get()处堵塞
            datestring = self.queueinput.get()
            if datestring == "EOF":
                break
            dateList.append(datestring)
        if len(dateList) == 1:
            dateList.append("test")
        return dateList

    @abstractmethod
    def update_sql_hook(self, dateList):
        """
        形成更新语句或其他语句
        :param dateList:
        :return:
        """
        pass

    def ExeSqlToMysql(self, sql):
        self.myutil.ExeSqlToDB(sql, errExit=True)


class DB3MysqlConvertion2(DB3MysqlConvertion):

    def input_sql_hook(self):
        sql = "select wanID from article where stat=2"
        return sql

    def input_row_hook(self, row):
        return row[0]

    def update_sql_hook(self, dateList):
        sql = "update modify_title_info set stat=1 where rawid in {}".format(tuple(dateList))
        return sql


def db3_sql_deal():
    db3mysql = DB3MysqlConvertion2()
    db3mysql.process(r'F:\test\db3')
    while True:
        listdate = db3mysql.get_id()
        if len(listdate) == 0:
            break
        sql = db3mysql.update_sql_hook(listdate)
        print(len(listdate))
        curpath = BaseDir.get_file_dir_absolute(__file__)
        configfile = BaseDir.get_new_path(curpath, "db.ini")
        db3mysql.conn_mysql(configfile, "db1")
        db3mysql.ExeSqlToMysql(sql)


def mysql_deal():
    db3mysql = DB3MysqlConvertion2()
    curpath = BaseDir.get_file_dir_absolute(__file__)
    configfile = BaseDir.get_new_path(curpath, "db.ini")
    db3mysql.conn_mysql(configfile, "db1")
    db3mysql.mysql_process()
    while True:
        listdate = db3mysql.get_id()
        if len(listdate) == 0:
            break
        sql = db3mysql.update_sql_hook(listdate)
        print(len(listdate))
        for filePath in BaseDir.get_dir_all_files(r"F:\test\db3"):
            sqliteutiles = Sqlite3Utiles().Sqlite3DBConnectFromFilePath(filePath, encoding="utf-8")
            sqliteutiles.ExeSqlliteSql(sql)


# db3_sql_deal()
# mysql_deal()
for filePath in BaseDir.get_dir_all_files(r"F:\test\db3"):
    sqliteutiles = Sqlite3Utiles().Sqlite3DBConnectFromFilePath(filePath, encoding="utf-8")
    rows = sqliteutiles.SelectFromSqlliteFetchall("select rawid from modify_title_info where stat=0")
    Listrow = []
    for row in rows:
        Listrow.append(row[0])
    sql = "insert ignore into article1(wanID) values (%s)"
    print(Listrow)
    db3mysql = DB3MysqlConvertion2()
    curpath = BaseDir.get_file_dir_absolute(__file__)
    configfile = BaseDir.get_new_path(curpath, "db.ini")
    db3mysql.conn_mysql(configfile, "db1")
    db3mysql.myutil.ExeSqlMany(sql, Listrow)
